
import java.sql.*;   // methods to process SQL commands
import java.util.ArrayList;
import java.util.Collections;

import javax.sql.*;  // ditto
//import org.postgresql.jdbc3.Jdbc3PoolingDataSource; // connection via JDBC to postgrSQL
import java.io.IOException;   // enable IOException objects & handling
import java.io.PrintWriter;   // enable PrintWriter (so can use .println())
import javax.servlet.http.*;  // provides servlet functionality
import javax.servlet.*;	      // ditto
import javax.*;


public class FindMoviesWithGenres extends HttpServlet
{
	//private static DataSource ds;	// contains DB and connection data; see init()
	private static Connection connection;
	//private static StringBuffer buf;
	private  int startIndex = 0;
	private boolean lastPage = false;
	
	public String issueQuery(String genreName, HttpServletRequest request)
    {	
		StringBuffer buf2 = new StringBuffer();
		// see the API doc at java.sun.com for details
		try
		{
			HttpSession session = request.getSession();
			MovieList movie_list = (MovieList) session.getAttribute(Constants.MovieListAttributeName);
			ResultSet resultMovie = null;
			if(request.getParameter("order") != null && (request.getParameter("order")).length() > 0)
			{
				//MovieList movie_list = (MovieList) session.getAttribute(Constants.MovieListAttributeName);
				
				if(request.getParameter("order").equals(Constants.ORDER_BY_TITLE))
				{
					if(movie_list.getOrderType().equals(Constants.ORDER_BY_TITLE))
						movie_list.reverseOrder();
					else
					{
						movie_list.setOrderType(Constants.ORDER_BY_TITLE);
						//movie_list.order = Constants.ASCENDING;
					}
					
					Statement select21 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
					resultMovie = select21.executeQuery(movie_list.getQuery() + " ORDER BY title " + movie_list.getOrder() + ", year DESC");
					//buf2.append("Looking for new order of same query : " +movie_list.getQuery() + " ORDER BY title " + movie_list.getOrder() + ", year DESC <br><br>");
					movie_list.setRS(resultMovie);
					movie_list.setStartIndex(0);
					session.setAttribute(Constants.MovieListAttributeName, movie_list);
					
			  }
				else if(request.getParameter("order").equals(Constants.ORDER_BY_YEAR) )
				{
					if(movie_list.getOrderType().equals(Constants.ORDER_BY_YEAR))
						movie_list.reverseOrder();
					else
					{
						movie_list.setOrderType(Constants.ORDER_BY_YEAR);
						//movie_list.order = Constants.ASCENDING;
					}
					
					Statement select22 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
					resultMovie = select22.executeQuery(movie_list.getQuery() + " ORDER BY year " + movie_list.getOrder());
					//buf2.append("Looking for new order of same query : " +movie_list.getQuery() + " ORDER BY year " + movie_list.getOrder()+" <br><br>");
					movie_list.setRS(resultMovie);
					movie_list.setStartIndex(0);
				}
				else if(request.getParameter("order").equals(Constants.ORDER_BY_DIRECTOR) )
				{
					if(movie_list.getOrderType().equals(Constants.ORDER_BY_DIRECTOR))
						movie_list.reverseOrder();
					else
					{
						movie_list.setOrderType(Constants.ORDER_BY_DIRECTOR);
						//movie_list.order = Constants.ASCENDING;
					}
					
					Statement select23 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
					resultMovie = select23.executeQuery(movie_list.getQuery() + " ORDER BY director_last_name " + movie_list.getOrder() + ", year DESC");
					//buf2.append("Looking for new order of same query : " +movie_list.getQuery() + " ORDER BY director_last_name " + movie_list.getOrder() + ", year DESC <br><br>");

					movie_list.setRS(resultMovie);
					movie_list.setStartIndex(0);
				}
				
				if(movie_list.getStart() == 0)
					movie_list.setStartIndex(0);
				
				movie_list.setState("firstPage");
				startIndex = movie_list.getStart();
				
				session.setAttribute(Constants.MovieListAttributeName, movie_list);
			}
			else if(request.getParameter("goTo") != null)
			{
				
				
				String goTo = request.getParameter("goTo");
				//buf2.append("doing go to: " + goTo + "<br><br>");
				
				if( movie_list.getState().equals("lastPage"))
				{
				
					int sizeOfList = movie_list.getSizeOfMovieList();	
					//buf2.append("RESULT OF GOING BACK FROM LAST PAGE: sizeOfList: " + sizeOfList);
					if(sizeOfList%20 == 0)
					{//	buf2.append("size is mod by 20");
						movie_list.setStartIndex(sizeOfList - 40);
						int shouldStartAt = sizeOfList -40;
						//buf2.append("movie list should be start at : " + shouldStartAt +"<br>");
						//buf2.append("movie list actually starting at : " + movie_list.getStart() +"<br>");
					}
					else
					{
						int index = 0;
						while(index < sizeOfList)
						{
							index += 20;
						}
						movie_list.setStartIndex(index-40);
						
						if(index-40 != movie_list.getStart())
								buf2.append("start index does not match changes");
						
						//buf2.append("CHANGED START INDEX: " + movie_list.getStart());
					}
					movie_list.setState("middlePage");
				}
				else if(goTo.equals(Constants.PREV))
				{
					//buf2.append("before state: " + movie_list.getState());
					//buf2.append("before - GOING TO PREVIOUS!! SHOULD START AT: " + movie_list.getStart() + "<br><br>");
					//int temp = start - 40;
					movie_list.setStartIndex(movie_list.getStart() - 40);
					//buf2.append("after - GOING TO PREVIOUS!! SHOULD START AT: " + movie_list.getStart()+ "<br><br>");
					movie_list.setState("middlePage");
				}
				
				if(movie_list.getStart() < 0 || goTo.equals(Constants.BEGIN))
				{
					//buf2.append("starting list again at zero");
					movie_list.setState("firstPage");
					movie_list.setStartIndex(0);
				}
				if(goTo.equals(Constants.NEXT))
				{
					//buf2.append("GOING NEXT, starting with index: " + movie_list.getStart());
					movie_list.setState("middlePage");
				}
				session.setAttribute(Constants.MovieListAttributeName, movie_list);
				resultMovie = movie_list.getMovieList();
				
			}
			else if(movie_list == null || !(movie_list.getMovieListType().equals(Constants.BROWSE_BY_GENRE)) || !(movie_list.getCurrentGenre().equals(genreName)))
			{
				//buf2.append("Building new resultset list <br><br>");
				// get and build string of test DB's metadata
				Statement select = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
				ResultSet result = select.executeQuery("Select * from genres where name ilike '%"+genreName+"%'");
				//buf2.append("QUERY: Select * from genres where name ilike '%"+genreName+"%' <br><br>");
	
				String tempQueryForMovieIds = "Select movie_id from genres_in_movies where ";
				while(result.next())
				{
					if(result.isLast())
						tempQueryForMovieIds += "genre_id = " + result.getInt(1);
					else
						tempQueryForMovieIds += "genre_id = " +result.getInt(1)+ " or ";
				}
				
				
				if(tempQueryForMovieIds.equals("Select movie_id from genres_in_movies where "))
					tempQueryForMovieIds = "Select movie_id from genres_in_movies";
				
				//buf2.append("Query for movie ids in genres_in_movies " + tempQueryForMovieIds + "<br><br>");
			
				Statement select10 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
				ResultSet resultMovieIds = select10.executeQuery(tempQueryForMovieIds);
				
				String tempQueryForMovies = "Select * from movies where ";
				while(resultMovieIds.next())
				{
					if(resultMovieIds.isLast())
						tempQueryForMovies += "id = " + resultMovieIds.getInt(1);
					else
						tempQueryForMovies += "id = " + resultMovieIds.getInt(1) + " or ";
						
				}
				
				//if there are no movies within this genre then return
				if(tempQueryForMovies.equals("Select * from movies where "))
				{
					buf2.append("There are no movies associated with <B>" +genreName+"</B>.");
					connection.close();
					return buf2.toString();
				}
					
				
				String tempString = tempQueryForMovies;
				tempQueryForMovies += " ORDER BY title, year DESC";
				
			//	buf2.append("Query for movies: " + tempQueryForMovies + "<br><br>");
				
			
				Statement select11 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
				resultMovie = select11.executeQuery(tempQueryForMovies);
				
				movie_list = new MovieList();
				movie_list.setMovieListType(Constants.BROWSE_BY_GENRE);
				movie_list.setRS(resultMovie);
				movie_list.setQuery(tempString);
				movie_list.setCurrentGenre(genreName);
				movie_list.setState("firstPage");
				session.setAttribute(Constants.MovieListAttributeName, movie_list);
				startIndex = movie_list.getStart() ;
			
			}
	
			
			//MovieList movie_list2 = (MovieList) session.getAttribute(Constants.MovieListAttributeName);
			//buf2.append("movie list start: " + movie_list.getStart());
		
			resultMovie.absolute(movie_list.getStart());
			startIndex = movie_list.getStart();
			
			int counter = 0;
			while(counter < 20 && resultMovie.next())
			{
				buf2.append("<TR valign=top>");
				buf2.append("<TD width=150><div class=\"wpmd\">");
				buf2.append("<div>");
				buf2.append("<input type=\"radio\" name=\"movieId\" value=\"" + resultMovie.getInt(1) + "\"> " + "<a href=\"SingleMovieTitle?id="+resultMovie.getInt(1)+"\"> " +resultMovie.getString(2) + "</a>");
				buf2.append("</div>");
				buf2.append("</TD>");
				
			
				buf2.append("<TD width=100><div class=\"wpmd\">");
				buf2.append("<div>");
				buf2.append( resultMovie.getInt(3));
				buf2.append("</div>");
				buf2.append("</TD>");
				
				
				buf2.append("<TD width=150><div class=\"wpmd\">");
				buf2.append("<div>");
				buf2.append(resultMovie.getString(7) + " "+ resultMovie.getString(4));
				buf2.append("</div>");
				buf2.append("</TD>");
				
				String genreAssociated = findGenres(Integer.toString(resultMovie.getInt(1)));
				//genres
				buf2.append("<TD width=150><div class=\"wpmd\">");
				buf2.append("<div>");
				buf2.append(genreAssociated);
				buf2.append("</div>");
				buf2.append("</TD>");
				

				String stars = findStars(Integer.toString(resultMovie.getInt(1)));
				//movie stars
				buf2.append("<TD width=200><div class=\"wpmd\">");
				buf2.append("<div>");
				buf2.append(stars);
				buf2.append("</div>");
				buf2.append("</TD>");
				
				buf2.append("</TR>");
				counter++;
				
			}
			if(!resultMovie.isAfterLast() && counter == 20)
			{
				//buf2.append("middle page or beginning page");
				movie_list.setStartIndex(resultMovie.getRow());
				lastPage = false;
			}
			if((resultMovie.isLast() &&  counter == 20) || resultMovie.isAfterLast())
			{		
				int tempInt = movie_list.getStart() + 20;
				movie_list.setStartIndex(tempInt);
				//buf2.append("last page! Ending list at: " + tempInt);
				//buf2.append("last page set start index: " + movie_list.getStart());
				lastPage = true;
				movie_list.setState("lastPage");
			}
		
			//buf2.append("Ending list at : " + movie_list.getStart());
			
			session.setAttribute(Constants.MovieListAttributeName, movie_list);
			connection.close();	
		}
		catch(NullPointerException e)
		{
			buf2.append("NullPointerException occured: " + e.getMessage() + "\n" + e.getStackTrace());
			
		}
		catch (SQLException sqle)
		{
			buf2.append("SQL exception occured: " + sqle.getMessage());
		}		
		
		// return buffered string of text
		
		return buf2.toString();
	}
	
	public  String findGenres(String movieId) throws SQLException
	{
		Statement select4 = connection.createStatement();
		ResultSet resultGenresID = select4.executeQuery("Select genre_id from genres_in_movies where movie_id = " + movieId);
		String temp = "";
		while(resultGenresID.next())
		{
			Statement select5 = connection.createStatement();
			ResultSet resultGenres = select5.executeQuery("Select * from genres where id = " + resultGenresID.getInt(1));
			if(resultGenres.next())
				temp += resultGenres.getString(2) + " <br>";
			
		}
	
		if(temp.equals(""))
			return "No genres";
		
		
		return temp;	
	}
	public String findStars(String movieId) throws SQLException
	{
		Statement select2 = connection.createStatement();
		ResultSet resultStarsID = select2.executeQuery("Select star_id from stars_in_movies where movie_id = " + movieId);
		String temp = "";
		
		while(resultStarsID.next())
		{
			Statement select3 = connection.createStatement();
			ResultSet resultStars = select3.executeQuery("Select * from stars where id = " + resultStarsID.getInt(1));
			if(resultStars.next())
			{
				temp += "<a href=\"SingleStarPage?id=" + resultStars.getInt(1) + "\"> " + resultStars.getString(2) + " " + resultStars.getString(3) + " </a><br>";							
			}
		
		}
	
		if(temp.equals(""))
			return "No movie stars";
		return temp;
	}
	// Print HTML for Web page heading
	public void doGet(HttpServletRequest request, HttpServletResponse response)
		throws IOException, ServletException
	{
		if (!Bridge.hasBeenToMainPage(request.getSession(), response.getWriter()))
		{
			return;
		}
		
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		String genreName = request.getParameter("genres");
		String goTo = request.getParameter("goTo");
		String order = request.getParameter("order");
		StringBuffer buf = new StringBuffer();
		
		try
		{
			Class.forName("org.postgresql.Driver").newInstance();
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost/fabflixs", "testuser", "testpass");
			HttpSession session = request.getSession();
			if(genreName == null && goTo == null && order == null 
					//|| genreName.length() == 0 && goTo.length() == 0 && order.length() == 0
					)
			{
				buf.append("<html>");
				buf.append("<head>");
				buf.append("No genre was chosen <br>");
				buf.append("<meta http-equiv=\"REFRESH\" content=\"0;url=BrowseByGenres\">");
				buf.append("</head>");
				buf.append("</HTML>");
				out.println(buf.toString());
			}
			else
			{
				//creating form
				buf.append("<form name=\"singleMovieTitle\" action=\"DvdProcessor\" method=\"POST\">");
				//out.println("<div align=\"left\"><br>");
				
				//making table
			
				buf.append("<div><TABLE bgcolor=\"#FFFFFF\" border=1 bordercolorlight=\"#C0C0C0\" bordercolordark=\"#808080\">");
				
				buf.append("<TR valign=top>");
		
			
				buf.append("<TD width=150><div class=\"wpmd\">");
				buf.append("<div>");
				buf.append("<a href=\"FindMoviesWithGenres?order=5\"><B>TITLE</B></a>");
				buf.append("</div>");
				buf.append("</TD>");
				
				
				buf.append("<TD width=100><div class=\"wpmd\">");
				buf.append("<div>");
				buf.append("<a href=\"FindMoviesWithGenres?order=6\"><B>YEAR</B></a>");
				buf.append("</div>");
				buf.append("</TD>");
				
				buf.append("<TD width=150><div class=\"wpmd\">");
				buf.append("<div>");
				buf.append("<a href=\"FindMoviesWithGenres?order=7\"><B>DIRECTOR</B></a>");
				buf.append("</div>");
				buf.append("</TD>");
				
				buf.append("<TD width=150><div class=\"wpmd\">");
				buf.append("<div>");
				buf.append("<B>GENRE</B>");
				buf.append("</div>");
				buf.append("</TD>");
				
				buf.append("<TD width=200><div class=\"wpmd\">");
				buf.append("<div>");
				buf.append("<B>MOVIE STARS</B>");
				buf.append("</div>");
				buf.append("</TD>");
				buf.append("</TR>");
				
				buf.append(issueQuery(genreName, request));
				
				buf.append("<TR valign=top>");

				buf.append("<TD colspan=5 align=center><div class=\"wpmd\">");
				buf.append("<div id=\"AddToCart\" \"><input type=\"image\" name=\"AddToCart\" src=\"../images/AddToCart.png\"></div>");
				buf.append("</TD>");
				buf.append("</TR>");
				buf.append("<TR valign=top>");
				
				if(lastPage)
				{
					
					buf.append("<TD colspan=5 align=left ><div class=\"wpmd\">");
					buf.append("<a href=\"FindMoviesWithGenres?goTo=2\">");
					buf.append("<div id=\"Prev\" \"><input type=\"image\" name=\"Prev\" src=\"../images/Prev.png\"></div></a>");
					buf.append("</TD>");
					//lastPage = false;
				}
				else if(startIndex != 0 && !lastPage)
				{
					//row for prev and next button
					//buf.append("<TR valign=top>");
					buf.append("<TD colspan=3 align=left ><div class=\"wpmd\">");
					buf.append("<a href=\"FindMoviesWithGenres?goTo=2\">");
					buf.append("<div id=\"Prev\" \"><input type=\"image\" name=\"Prev\" src=\"../images/Prev.png\"></div></a>");
					buf.append("</TD>");
				
					
				
					buf.append("<TD colspan=2 align=right ><div class=\"wpmd\">");
					buf.append("<a href=\"FindMoviesWithGenres?goTo=1\">");
					buf.append("<div id=\"Next\" \"><input type=\"image\" name=\"Next\" src=\"../images/Next.png\"></div></a>");
					buf.append("</TD>");
				}
				else if(startIndex == 0 && !lastPage)
				{
					
					buf.append("<TD colspan=5 align=right ><div class=\"wpmd\">");
					buf.append("<a href=\"FindMoviesWithGenres?goTo=1\">");
					buf.append("<div id=\"Next\" \"><input type=\"image\" name=\"Next\" src=\"../images/Next.png\"></div></a>");
					buf.append("</TD>");
					
				}
				
				buf.append("</TR>");
				
				
				buf.append("</TABLE>");
				buf.append("</div>");
			
		
				buf.append("</form>");
				out.println(Template.getTemplateHTML("Movies Associated with Genre", buf.toString(), Constants.straightToCart,
						request.getSession() ));
			}
			
		}
		catch (SQLException sqle)
		{
			buf.append("SQL exception occured");
		}	
		catch (ClassNotFoundException cnfe)
		{
			buf.append("ClassNotFound exception occured");
		}
		catch (InstantiationException ie)
		{
			buf.append("Instantiation exception occured");
		}
		catch (IllegalAccessException iae)
		{
			buf.append("IllegalAccess exception occured");
		}
	
	}
	
	// Upon init of servlet, open the moviedb database
	public void init() 
	{
	}

	
}


